Possible regression: setNull() usage changed from 7.4 to 8.0 and up - Mailing list pgsql-jdbc

From Jeff Hubbach
Subject Possible regression: setNull() usage changed from 7.4 to 8.0 and up
Date
Msg-id r02010500-1046-FFD4AE24CB4411DAA9C0000D93451082@[205.238.79.158]
Whole thread Raw
Responses Re: Possible regression: setNull() usage changed from 7.4 to  (Kris Jurka <books@ejurka.com>)
List pgsql-jdbc
In the process of upgrading an app, I came across a change in behavior
of PreparedStatement.setNull(). The behavior of the driver for 7.3 and
7.4 is consistent, a call to:
  stmt.setNull(1,java.sql.Types.NULL);
succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
call fails with a "Could not determine data type" error.

The following calls also fail:
  stmt.setNull(1,java.sql.Types.OTHER);
  stmt.setObject(1,null);
  stmt.setObject(1,null,java.sql.Types.NULL);
  stmt.setObject(1,null,java.sql.Types.OTHER);

Changelog for Version dev401 (2005-07-24):
 * Improve null handling. Allow setNull(1, Types.XXX) for ARRAY, NULL,
DISTINCT, and STRUCT by mapping them to the unknown oid.

Changelog for Version 8.0-310 (2005-02-02):
 * Implement the Describe Statement protocol message. Reallow untyped
nulls and get the backend to resolve the type for us (if necessary).

Changelog for Version 8.0beta1-308 (2004-11-09):
 * Disallow these cases as we have insufficient type information:
setNull(i,Types.OTHER), setObject(i,null),
setObject(i,null,Types.OTHER).

Similar thread on the mailing list archives:
http://archives.postgresql.org/pgsql-jdbc/2005-02/msg00144.php

JDBC Drivers tested:
  pg73jdbc3.jar
  pg74.216.jdbc3.jar
  postgresql-8.1-315.jdbc3.jar
  postgresql-8.2dev-501.jdbc3.jar

Server Version:
  PostgreSQL 8.1.3

Stack Trace (using postgresql-8.1-315.jdbc3.jar):
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
could not determine data type of parameter $1
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(
QueryExecutorImpl.java:1512)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(
QueryExecutorImpl.java:1297)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
188)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
AbstractJdbc2Statement.java:437)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
AbstractJdbc2Statement.java:353)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
AbstractJdbc2Statement.java:257)
        at JDBCTest.main(JDBCTest.java:20)

Test Case:
createTestTable.sql
===================
create table test_null (test_null integer);

JDBCTest.java
=============
import java.sql.*;

public class JDBCTest {
public static void main(String[] args) throws Exception {
String url = "jdbc:postgresql://***/***";
String uname = "***";
String pword = "***";

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(url,uname,pword);

PreparedStatement st = conn.prepareStatement("select count(*) from
test_null where ? is null");
st.setNull(1,java.sql.Types.NULL);    // Fails
//st.setNull(1,java.sql.Types.OTHER);   // Fails
//st.setNull(1,java.sql.Types.INTEGER); // Works
//st.setObject(1,null);                 // Fails
//st.setObject(1,null,java.sql.Types.NULL);   // Fails
//st.setObject(1,null,java.sql.Types.OTHER);  // Fails
//st.setString(1,null);                 // Works
ResultSet rs = st.executeQuery();
while(rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
conn.close();
}
}

pgsql-jdbc by date:

Previous
From: kevin@mtel.co.uk
Date:
Subject: Re: postgres jdbc transaction problem surfaces at times
Next
From: Kris Jurka
Date:
Subject: Re: Possible regression: setNull() usage changed from 7.4 to